package com.drophair.jspwork.bean;

import com.mysql.cj.jdbc.CallableStatement;
import com.sun.xml.internal.ws.server.ServerRtException;

import java.beans.VetoableChangeListener;
import java.sql.*;
import java.util.Vector;

public class Sqlgoods {
    private static String driverName="com.mysql.cj.jdbc.Driver";
    private static String localHost="127.0.0.1";
    private static String mysqlPort="3306";
    private static String userName="root";
    private static String userPwd="123456";
    private static String dbBase="javadb";
    private static String url=String.format("jdbc:mysql://%s:%s/%s?useSSL=false&serverTimezone=UTC",localHost,mysqlPort,dbBase);

    public Vector<Vector<String>> getGoodsInfo() throws ClassNotFoundException, SQLException {
        Vector<Vector<String>> vector=new Vector<Vector<String>>();
        String sql="select * from goodsinfo;";
        Class.forName(driverName);
        Connection connection = DriverManager.getConnection(url,userName,userPwd);
        Statement statement=connection.createStatement();
        ResultSet resultSet=statement.executeQuery(sql);
        while (resultSet.next()){
            Vector<String> v=new Vector<String>();
            for(int i=1;i<=3;i++){
                v.add(resultSet.getString(i));
            }
            vector.add(v);
        }
        connection.close();
        return vector;
    }

    public Vector<String> getGoodInfo(String goodid) throws ClassNotFoundException, SQLException {
        Vector<String> vector=new Vector<String>();
        Class.forName(driverName);
        Connection connection=DriverManager.getConnection(url,userName,userPwd);
        String sql="";
        sql=String.format("select * from goodsinfo where goodsid='%s';",goodid);
        System.out.println(sql);
        Statement statement=connection.createStatement();
        ResultSet resultSet=statement.executeQuery(sql);

        while (resultSet.next()) {
            for(int idx=1;idx<=3;idx++){
                String tmp="";
                tmp=resultSet.getString(idx);
                System.out.println(tmp);
                vector.add(tmp);
            }
        }
        return vector;
    }

    public Vector<Vector<String>> getCartInfo() throws ClassNotFoundException, SQLException {
        Vector<Vector<String>> vector=new Vector<Vector<String>>();
        String sql="select * from cartinfo;";
        Class.forName(driverName);
        Connection connection = DriverManager.getConnection(url,userName,userPwd);
        Statement statement=connection.createStatement();
        ResultSet resultSet=statement.executeQuery(sql);
        while (resultSet.next()){
            Vector<String> v=new Vector<String>();
            for(int i=1;i<=2;i++){
                v.add(resultSet.getString(i));
            }
            vector.add(v);
        }
        connection.close();
        return vector;
    }

    public boolean addCart(String goodid,String goodname) throws SQLException, ClassNotFoundException {
        boolean addSign=false;
        String sql="select count(*) from cartinfo where goodid='"+goodid+"';";
        Class.forName(driverName);
        Connection connection=DriverManager.getConnection(url,userName,userPwd);
        Statement statement=connection.createStatement();
        ResultSet resultSet=statement.executeQuery(sql);
        int goodcount=0;
        while (resultSet.next()){
            goodcount=Integer.parseInt(resultSet.getString(1));
        }
        if(goodcount>0){
            addSign=false;
        }else{
            addSign=true;
            sql=String.format("insert into cartinfo values ('%s','%s')",goodid,goodname);
            statement.executeUpdate(sql);
        }
        connection.close();
        return addSign;
    }

    public boolean delCart(Vector<String> goodids) throws ClassNotFoundException, SQLException {
        boolean delSign=false;
        Class.forName(driverName);
        Connection connection=DriverManager.getConnection(url,userName,userPwd);
        Statement statement=connection.createStatement();
        String sql="";
        for(int i=0;i<goodids.size();i++){
            delSign=true;
            sql=String.format("delete from cartinfo where goodid='%s';", goodids.get(i));
            statement.executeUpdate(sql);
        }
        connection.close();
        return delSign;
    }

    public boolean delAllCart() throws ClassNotFoundException, SQLException {
        boolean delSign=false;
        Class.forName(driverName);
        Connection connection=DriverManager.getConnection(url,userName,userPwd);
        Statement statement=connection.createStatement();
        String sql="";
        sql=String.format("delete from cartinfo;");
        statement.executeUpdate(sql);
        connection.close();
        return delSign;
    }

    public boolean addGood(String goodname,double goodprice) throws SQLException, ClassNotFoundException {
        Vector<Vector<String>> vectors=getGoodsInfo();
        int goodid=vectors.size();
        String sql=String.format("insert into goodsinfo values('%d','%s',%f)",goodid+1,goodname,goodprice);
        boolean sign=false;
        Class.forName(driverName);
        Connection connection=DriverManager.getConnection(url,userName,userPwd);
        Statement statement=connection.createStatement();
        System.out.println(sql);
        int idx=statement.executeUpdate(sql);
        if(idx==1){
            sign=true;
        }
        connection.close();
        return sign;
    }
}
